﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Web;
using Microsoft.Office.Interop.Excel;
using MSExcel = Microsoft.Office.Interop.Excel;

namespace KPWeb.Class
{
    public class ExcelFunction
    {
        class ExcelReaderInterop
        {
            /// <summary>
            /// Store the Application object we can use in the member functions.
            /// </summary>
            MSExcel.Application _excelApp;

            /// <summary>
            /// Initialize a new Excel reader. Must be integrated
            /// with an Excel interface object.
            /// </summary>
            public ExcelReaderInterop()
            {
                _excelApp = new MSExcel.Application();
            }

            public void ExcelOpenSpreadsheets(string thisFileName)
            {
                try
                {
                    //
                    // This mess of code opens an Excel workbook. I don't know what all
                    // those arguments do, but they can be changed to influence behavior.
                    //
                    MSExcel.Workbook workBook = _excelApp.Workbooks.Open(thisFileName,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing);

                    //
                    // Pass the workbook to a separate function. This new function
                    // will iterate through the worksheets in the workbook.
                    //
                    ExcelScanIntenal(workBook);

                    //
                    // Clean up.
                    //
                    workBook.Close(false, thisFileName, null);
                    Marshal.ReleaseComObject(workBook);
                }
                catch
                {
                    //
                    // Deal with exceptions.
                    //
                }
            }



            /// <summary>
            /// Scan the selected Excel workbook and store the information in the cells
            /// for this workbook in an object[,] array. Then, call another method
            /// to process the data.
            /// </summary>
            private void ExcelScanIntenal(Workbook workBookIn)
            {
                //
                // Get sheet Count and store the number of sheets.
                //
                int numSheets = workBookIn.Sheets.Count;

                //
                // Iterate through the sheets. They are indexed starting at 1.
                //
                for (int sheetNum = 1; sheetNum < numSheets + 1; sheetNum++)
                {
                    Worksheet sheet = (Worksheet)workBookIn.Sheets[sheetNum];

                    //
                    // Take the used range of the sheet. Finally, get an object array of all
                    // of the cells in the sheet (their values). You can do things with those
                    // values. See notes about compatibility.
                    //
                    Range excelRange = sheet.UsedRange;
                    object[,] valueArray = (object[,])excelRange.get_Value(
                        XlRangeValueDataType.xlRangeValueDefault);

                    //
                    // Do something with the data in the array with a custom method.
                    //
                    ProcessObjects(valueArray);
                }
            }

            private void ProcessObjects(object[,] valueArray)
            {
                throw new NotImplementedException();
            }










        }







        //private void CreateNewWorkbook(Tables table)
        //{
        //    MSExcel.Application app = null;
        //    MSExcel.Workbook book = null;
        //    MSExcel.Worksheet sheet = null;

        //    try
        //    {
        //        string startPath = System.IO.Path.GetDirectoryName(System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName);
        //        string filePath = System.IO.Path.Combine(startPath, "sal1011forms.xls");
        //        Microsoft.Win32.SaveFileDialog sfd = new Microsoft.Win32.SaveFileDialog();

        //        app = new MSExcel.Application();
        //        book = app.Workbooks.Open(filePath);
        //        sheet = (MSExcel.Worksheet)book.Worksheets.get_Item((int)table + 1);

        //        sfd.AddExtension = true;
        //        sfd.FileName = table.ToString() + ".xls";
        //        sfd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);

        //        if (sfd.ShowDialog() == true)
        //        {
        //            sheet.SaveAs(sfd.FileName);
        //        }
        //    }
        //    finally
        //    {
        //        if (book != null)
        //        {
        //            book.Close();
        //        }
        //        if (app != null)
        //        {
        //            app.Quit();
        //        }
        //        this.ReleaseObject(sheet);
        //        this.ReleaseObject(book);
        //        this.ReleaseObject(app);
        //    }
        //}





    }
}